﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using Cosmetic.DataAccess;
using Cosmetic.DataEntity;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Cosmetic.Logic
{
    /// <summary>
    /// Podeuct manager class
    /// </summary>
    public class ProductManager : LogicBase
    {
        private CategoryManager m_CategoryManager = new CategoryManager();
        private CommentManager m_CommentManager = new CommentManager();
        private TagManager m_TagManager = new TagManager();

        /// <summary>
        /// Insert a new product into database
        /// </summary>
        /// <param name="aProduct">new product</param>
        public void Insert(Product aProduct)
        {
            m_CosmeticDC.Products.InsertOnSubmit(aProduct);
            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// Insert a new product and product fit into database
        /// </summary>
        /// <param name="aProduct">new product</param>
        /// <param name="aProductFitList">new product fit</param>
        public void Insert(Product aProduct, List<ProductFit> aProductFitList)
        {
            m_CosmeticDC.Products.InsertOnSubmit(aProduct);
            m_CosmeticDC.SubmitChanges();
            aProduct.ProductFits.AddRange(from theProductFit in aProductFitList
                                          where theProductFit.Score != 0
                                          select theProductFit);
            //m_CosmeticDC.ProductFits.InsertAllOnSubmit(aProductFitList.Select(theProductFit=>theProductFit.Score!=0));
            m_CosmeticDC.SubmitChanges();
        }

        public IEnumerable GetNewArrivalProductNew()
        {
            var result = from theNewArrival in m_CosmeticDC.NewArrivalProducts
                         join theProduct in m_CosmeticDC.ProductInfos
                         on theNewArrival.ProductId equals theProduct.ProductId
                         orderby theNewArrival.Sequence ascending
                         select new
                         {
                             Id = theNewArrival.Id,
                             ProductId = theProduct.ProductId,
                             Name = theProduct.Name,
                             Views = theProduct.Views,
                             PicUrl = theProduct.PictureUrl,
                             Sequence = theNewArrival.Sequence,
                             Price = theProduct.网络零售价,
                             Description = theProduct.Description
                         };
            return result;
        }

        public void InsertNewArrivalProduct(string ProductId)
        {
            NewArrivalProduct theNew = new NewArrivalProduct();
            theNew.ProductId = new Guid(ProductId);
            var result = from theMax in m_CosmeticDC.NewArrivalProducts orderby theMax.Sequence descending select theMax;

            if (result.Count() == 0)
            {
                theNew.Sequence = 0;
            }
            else
            {
                theNew.Sequence = (from theMax in m_CosmeticDC.NewArrivalProducts orderby theMax.Sequence descending select theMax).First().Sequence + 1;
            }

            m_CosmeticDC.NewArrivalProducts.InsertOnSubmit(theNew);
            m_CosmeticDC.SubmitChanges();
        }

        public void DeleteNewArrivalProduct(int Id)
        {
            var result = (from theNew in m_CosmeticDC.NewArrivalProducts
                          where theNew.Id == Id
                          select theNew).Single();

            m_CosmeticDC.NewArrivalProducts.DeleteOnSubmit(result);
            m_CosmeticDC.SubmitChanges();
        }

        public void UpdateNewArrivalProduct(int Id, int Sequence, Guid ProductId)
        {
            var result = (from theNew in m_CosmeticDC.NewArrivalProducts
                          where theNew.Id == Id
                          select theNew).Single();
            result.Sequence = Sequence;
            result.ProductId = ProductId;
            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// Delete product
        /// </summary>
        /// <param name="aProduct">The product want delete</param>
        public void Delete(Product aProduct)
        {
            m_CosmeticDC.ProductFits.DeleteAllOnSubmit(aProduct.ProductFits);
            m_CosmeticDC.SubmitChanges();

            List<Comment> theCommentList = aProduct.Comments.ToList();
            foreach (Comment theComment in theCommentList)
            {
                this.m_CommentManager.Delete(theComment);
            }

            m_CosmeticDC.Products.DeleteOnSubmit(aProduct);
            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// update the product and product fit
        /// </summary>
        /// <param name="aProduct">the specify product</param>
        /// <param name="aProductFitList">the product fit</param>
        public void Update(Product aProduct, List<ProductFit> aProductFitList)
        {
            m_CosmeticDC.ProductFits.DeleteAllOnSubmit(aProduct.ProductFits);
            aProduct.ProductFits.AddRange(from theProductFit in aProductFitList
                                          where theProductFit.Score != 0
                                          select theProductFit);
            m_CosmeticDC.SubmitChanges();
        }



        /// <summary>
        /// check the Product whether or not already exist.
        /// </summary>
        /// <param name="aBrand">Brand</param>
        /// <param name="aProductName">Product name</param>
        /// <returns>if exists return true else return false</returns>
        public bool CheckExists(Brand aBrand, string aProductName)
        {
            return this.CheckExists(aBrand.BrandId, aProductName);
        }
        /// <summary>
        /// check the Product whether or not already exist.
        /// </summary>
        /// <param name="aBrandId">Brand Id</param>
        /// <param name="aProductName">Product name</param>
        /// <returns>if exists return true else return false</returns>
        public bool CheckExists(Guid aBrandId, string aProductName)
        {
            List<Product> theProductList = (from theProduct in m_CosmeticDC.Products
                                            where theProduct.Name == aProductName && theProduct.BrandId == aBrandId
                                            select theProduct).ToList();
            return theProductList.Count > 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductCode"></param>
        /// <returns></returns>
        public bool CheckExists(string aProductCode)
        {
            List<Product> theProductList = (from theProduct in m_CosmeticDC.Products
                                            where theProduct.ProductCode == aProductCode
                                            select theProduct).ToList();
            return theProductList.Count > 0;
        }
        public bool CheckExistes(string Productname)
        {
            List<Product> theProductList = (from theProduct in m_CosmeticDC.Products
                                            where theProduct.Name == Productname

                                            select theProduct).ToList();
            return theProductList.Count > 0;
        }

        /// <summary>
        /// check the Product name whether or not already exist in the brand.
        /// </summary>
        /// <param name="aBrand">brand</param>
        /// <param name="aProductName">product name</param>
        /// <param name="aProductId">product id</param>
        /// <returns>if exists return true else return false</returns>
        public bool CheckName(Brand aBrand, string aProductName, Guid aProductId)
        {
            return this.CheckName(aBrand.BrandId, aProductName, aProductId);
        }
        /// <summary>
        /// check the Product name whether or not already exist in the brand.
        /// </summary>
        /// <param name="aBrandId">brand id</param>
        /// <param name="aProductName">product name</param>
        /// <param name="aProductId">product id</param>
        /// <returns>if exists return true else return false</returns>
        public bool CheckName(Guid aBrandId, string aProductName, Guid aProductId)
        {
            List<Product> theProductList = (from theProduct in m_CosmeticDC.Products
                                            where theProduct.Name == aProductName && theProduct.BrandId == aBrandId && theProduct.ProductId != aProductId
                                            select theProduct).ToList();
            return theProductList.Count > 0;
        }

        /// <summary>
        /// Get product by product Id
        /// </summary>
        /// <param name="aProductId">Product Id</param>
        /// <returns>Product</returns>
        public Product GetProductById(Guid? aProductId)
        {
            return m_CosmeticDC.Products.Single(theProect => theProect.ProductId == aProductId);
        }



        public Product GetProductById1(Guid aProductId)
        {
            var result = (from theProduct in m_CosmeticDC.Products
                          where theProduct.ProductId == aProductId
                          select theProduct).Single();
            return result;
        }

        public Product GetProductByUID(string aUID)
        {
            var result = (from theProduct in m_CosmeticDC.Products
                          where theProduct.UID == aUID
                          select theProduct).Single();
            return result;
        }

        public ProductInfo GetProductByIdes(Guid aProductId)
        {
            return m_CosmeticDC.ProductInfos.Single(theProect => theProect.ProductId == aProductId);
        }

        public ProductInfo GetProductByIdes1(Guid aProductId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductId == aProductId
                          select theProduct).Single();
            return result;
        }

        public List<ProductInfo> GetProductByIds(Guid? aProductId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductId == aProductId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        public ProductInfo GetProductByids(Guid? aProductId)
        {
            return m_CosmeticDC.ProductInfos.Single(theProect => theProect.ProductId == aProductId);
        }

        /// <summary>
        /// get product's comments average score
        /// </summary>
        /// <param name="aProductId">product id</param>
        /// <returns>average score</returns>
        public double GetAverageScoreByProduct(Guid aProductId)
        {
            List<Comment> theCommentList = this.m_CommentManager.GetCommentByProduct(aProductId);
            if (0 == theCommentList.Count)
            {
                return 0;
            }
            return theCommentList.Average(theComment => theComment.Score);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductCode"></param>
        /// <returns></returns>
        public List<Product> GetProductByProductCode(string aProductCode)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.ProductCode == aProductCode
                    orderby theProduct.Name descending
                    select theProduct).ToList();
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }

        public List<ProductInfo> GetProductInfoByProductCode(string aProductCode)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductCode == aProductCode
                          orderby theProduct.Name descending
                          select theProduct).ToList();

            return result;
        }

        public IEnumerable GetAllClass(string Name)
        {
            var result = from theFocus in m_CosmeticDC.Products
                         join theProduct in m_CosmeticDC.Categories
                         on theFocus.ProductCategoryId equals theProduct.CategoryId
                         where theFocus.Name == Name
                         select new
                         {
                             SupCategoryId = theProduct.SupCategoryId,
                             CategoryId = theProduct.CategoryId,
                             Name = theFocus.ProductCategory.SupCategory.Name,
                             Names = theFocus.ProductCategory.Name,

                         };
            return result;
        }




        /// <summary>
        /// get product by brand id
        /// </summary>
        /// <param name="aBrandId">brand id</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByBrand(Guid aBrandId)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.BrandId == aBrandId
                    orderby theProduct.Name descending
                    select theProduct).ToList();
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }
        /// <summary>
        /// get product by brand
        /// </summary>
        /// <param name="aBrand">brand</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByBrand(Brand aBrand)
        {
            return this.GetProductByBrand(aBrand.BrandId);
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }
        /// <summary>
        /// get product by series
        /// </summary>
        /// <param name="aSeries">series</param>
        /// <returns>product list</returns>
        public List<Product> GetProductBySeries(ProductSeries aSeries)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.ProductSeriesId == aSeries.ProductSeriesId
                    orderby theProduct.Name descending
                    select theProduct).ToList();
            //return aSeries.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }
        /// <summary>
        /// get product by brand order by comment count
        /// </summary>
        /// <param name="aBrandId">the brand id</param>
        /// <param name="aStartDate"> comment create date after the startdate</param>
        /// <param name="anEndDate">comment create date before the end date</param>
        /// <param name="aCount">how mang product wanna get</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByBrandOrderComments(Guid aBrandId, DateTime aStartDate, DateTime anEndDate, int aCount)
        {
            return (from theProduct in m_CosmeticDC.Products
                    join theComment in m_CosmeticDC.Comments on theProduct.ProductId equals theComment.ProductId
                    where theProduct.BrandId == aBrandId
                    && aStartDate.Date <= theComment.CreateTime.Date
                    && theComment.CreateTime.Date <= anEndDate.Date
                    orderby theProduct.Comments.Count descending
                    select theProduct).Distinct().Take(aCount).ToList();
        }
        /// <summary>
        /// get product by category order by comment count
        /// </summary>
        /// <param name="aCategoryId">the category id</param>
        /// <param name="aStartDate"> comment create date after the startdate</param>
        /// <param name="anEndDate">comment create date before the end date</param>
        /// <param name="aCount">how mang product wanna get</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategoryOrderComments(Guid aCategoryId, DateTime aStartDate, DateTime anEndDate, int aCount)
        {
            return (from theProduct in m_CosmeticDC.Products
                    join theComment in m_CosmeticDC.Comments on theProduct.ProductId equals theComment.ProductId
                    where theProduct.ProductCategoryId == aCategoryId
                    && aStartDate.Date <= theComment.CreateTime.Date
                    && theComment.CreateTime.Date <= anEndDate.Date
                    orderby theProduct.Comments.Count descending
                    select theProduct).Distinct().Take(aCount).ToList();
        }
        /// <summary>
        /// fuzzy search product by the part or full product name
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName)
        {
            return this.GetProductByName(aProductName, m_CosmeticDC.Products.ToList());
        }



        public Product GetProductByNames(string aProductName)
        {
            List<Product> theUserList = (from theUser in m_CosmeticDC.Products
                                         where theUser.Name.ToLower() == aProductName.ToLower()
                                         select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }
        /// <summary>
        /// fuzzy search product by the part or full product name
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aProductList">search range</param>
        /// <returns>product list</returns>
        private List<Product> GetProductByName(string aProductName, List<Product> aProductList)
        {
            return (from theProduct in aProductList
                    where theProduct.Name.ToLower().Contains(aProductName.ToLower())
                    orderby theProduct.Name
                    select theProduct).ToList();
        }

        public List<ProductInfo> GetProductInfoByName(string aProductName)
        {
            var result = (from theProductInfo in m_CosmeticDC.ProductInfos
                          where theProductInfo.Name.ToLower().Contains(aProductName.ToLower())
                          orderby theProductInfo.Name
                          select theProductInfo).ToList();
            return result;
        }


        /// <summary>
        /// specified the brand fuzzy search product by the part or full product name
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aBrand">the specified brand</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName, Brand aBrand)
        {
            return this.GetProductByName(aProductName, this.GetProductByBrand(aBrand));
        }
        /// <summary>
        /// specified the series fuzzy search product by the part or full product name 
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aSeries">the specified series</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName, ProductSeries aSeries)
        {
            return this.GetProductByName(aProductName, this.GetProductBySeries(aSeries));
        }
        /// <summary>
        /// specified the category fuzzy search product by the part or full product name 
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aCategory">the specified series</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName, Category aCategory)
        {
            return this.GetProductByName(aProductName, this.GetProductByCategory(aCategory));
        }
        /// <summary>
        /// get all products
        /// </summary>
        /// <returns>all products</returns>
        public List<Product> GetAll()
        {
            return m_CosmeticDC.Products.ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> GetAllProductInfo()
        {
            return m_CosmeticDC.ProductInfos.Distinct().ToList();
        }
        /// <summary>
        /// get product by product category id
        /// </summary>
        /// <param name="aCategoryId">product category id</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.Products
                          where theProduct.ProductCategoryId == aCategoryId
                          orderby theProduct.Name
                          select theProduct).ToList();
            return result;
        }
        /// <summary>
        /// get product by product category 
        /// </summary>
        /// <param name="aCategory">product category</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Category aCategory)
        {
            return this.GetProductByCategory(aCategory.CategoryId);
        }
        /// <summary>
        /// get product by product category id
        /// </summary>
        /// <param name="aCategoryId">product category id</param>
        /// <param name="aName">part or full product name</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Guid aCategoryId, string aName)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.ProductCategoryId == aCategoryId
                    && theProduct.Name.Contains(aName)
                    orderby theProduct.Name
                    select theProduct).ToList();
        }
        /// <summary>
        /// get product by product category 
        /// </summary>
        /// <param name="aCategory">product category</param>
        /// <param name="aName">part or full product name</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Category aCategory, string aName)
        {
            return this.GetProductByCategory(aCategory.CategoryId, aName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductId"></param>
        /// <returns></returns>
        public ProductInfo GetProductInfoById(Guid aProductId)
        {
            return m_CosmeticDC.ProductInfos.Single(theProectInfo => theProectInfo.ProductId == aProductId);
        }

        /// <summary>
        /// delete the database named SHangHaiHZ.
        /// </summary>
        /// <param name="aProductId"></param>
        /// <returns></returns>
        public Product GetProductInfoById_Con(Guid aProductId)
        {
            return m_CosmeticDC.Products.Single(theProectInfo => theProectInfo.ProductId == aProductId);
        }

        /// <summary>
        /// get all products
        /// </summary>
        /// <returns>all products</returns>
        public List<ProductInfo> GetAll_L()
        {
            return m_CosmeticDC.ProductInfos.ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> GetNewArrivalProduct()
        {
            var result = (from theProductInfo in m_CosmeticDC.ProductInfos
                          join theNewProduct in m_CosmeticDC.NewArrivalProducts
                          on theProductInfo.ProductId equals theNewProduct.ProductId
                          orderby theNewProduct.Sequence ascending
                          select theProductInfo).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProduct"></param>
        /// <returns></returns>
        public List<ProductInfo> GetSimilarProductInfo(Product aProduct)
        {
            if (aProduct.Name.Contains("（"))
            {
                return (from theProductInfo in m_CosmeticDC.ProductInfos
                        where theProductInfo.Name.StartsWith(aProduct.Name.Substring(0, aProduct.Name.IndexOf('（'))) && theProductInfo.ProductId != aProduct.ProductId
                        orderby theProductInfo.ProductCode
                        select theProductInfo).ToList();
            }
            else
            {
                return null;
            }
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProduct"></param>
        /// <returns></returns>
        public List<ProductSize> GetProductSize(Product aProduct)
        {
            return (from theProductSize in m_CosmeticDC.ProductSizes
                    where theProductSize.GoodsCode == aProduct.ProductCode
                    orderby theProductSize.Size
                    select theProductSize).ToList();
        }

        public List<ProductSize> GetProductSize(Guid ProductId)
        {
            string ProductCode = (from theProduct in m_CosmeticDC.Products
                                  where theProduct.ProductId == ProductId
                                  select theProduct.ProductCode).Single();

            return (from theProductSize in m_CosmeticDC.ProductSizes
                    where theProductSize.GoodsCode == ProductCode
                    orderby theProductSize.Size
                    select theProductSize).ToList();

        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetRecommandProductInfo()
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.Recommend.GetValueOrDefault()
                    orderby theProductInfo.RecommendTime descending
                    select theProductInfo).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetFocusProductInfo(int count)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.Focus.GetValueOrDefault()
                    orderby theProductInfo.RecommendTime descending
                    select theProductInfo).Take(count).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetCurrentMonthSalesProductInfo(int count)
        {
            var result = from theMonthSale in m_CosmeticDC.MonthlySales
                         join theproduct in m_CosmeticDC.ProductInfos
                         on theMonthSale.ProductId equals theproduct.ProductId
                         orderby theMonthSale.Sequence
                         select new
                         {
                             ProductId = theproduct.ProductId,
                             ProductName = theproduct.Name,
                             PictureUrl = theproduct.PictureUrl,
                             ProductPrice = theproduct.网络零售价
                         };
            return result.Take(count);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public MonthlySaleImage GetMonthlySaleImage()
        {
            return m_CosmeticDC.MonthlySaleImages.First();
        }

        public void UpdateMonthlySaleImage(string PicUrl, string ProductId)
        {
            MonthlySaleImage dd = m_CosmeticDC.MonthlySaleImages.First();
            dd.PicUrl = PicUrl;
            dd.ProductId = new Guid(ProductId);

            m_CosmeticDC.SubmitChanges();
        }



        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public WeeklySaleImage GetWeeklySaleImage()
        {
            return m_CosmeticDC.WeeklySaleImages.First();
        }

        public void UpdateWeeklySaleImage(string PicUrl, string ProductId)
        {
            WeeklySaleImage dd = m_CosmeticDC.WeeklySaleImages.First();
            dd.PicUrl = PicUrl;
            dd.ProductId = new Guid(ProductId);

            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetCurrentWeeklySalesProductInfo(int count)
        {
            var result = from theWeeklySale in m_CosmeticDC.WeeklySales
                         join theproduct in m_CosmeticDC.ProductInfos
                         on theWeeklySale.ProductId equals theproduct.ProductId
                         orderby theWeeklySale.Sequence
                         select new
                         {
                             ProductId = theproduct.ProductId,
                             ProductName = theproduct.Name,
                             PictureUrl = theproduct.PictureUrl,
                             ProductPrice = theproduct.网络零售价
                         };
            return result.Take(count);
        }


        public List<ProductInfo> GetCurrentMonthFocusProductInfo(int count)
        {

            //select a.ProductId,count(*) number from dbo.OrderDetail a join dbo.[Order] b on a.OrderId = b.OrderId
            //where b.CreateTime < getdate()
            //group by a.ProductId 
            //order by number desc




            //return (from theProductInfo in m_CosmeticDC.ProductInfos
            //        where theProductInfo.Focus.GetValueOrDefault()
            //        && theProductInfo.RecommendTime.Value.Month == DateTime.Today.Month
            //        orderby theProductInfo.RecommendTime descending
            //        select theProductInfo).ToList();

            var result = from theOrderDetail in m_CosmeticDC.OrderDetails
                         join theOrder in m_CosmeticDC.Orders
                             on theOrderDetail.OrderId equals theOrder.OrderId
                         where theOrder.CreateTime.Month == DateTime.Today.Month
                         select theOrderDetail;
            var secondresult = (from theItem in result
                                group theItem by theItem.ProductId into g
                                select new
                                {
                                    g.Key,
                                    TotalPrice = g.Count()
                                });
            var finalresult = from item in secondresult
                              orderby item.TotalPrice descending
                              select new
                              {
                                  key = item.Key.ToString()
                              };

            var ffresult = (from it in finalresult
                            join dd in m_CosmeticDC.ProductInfos
                            on it.key equals dd.ProductId.ToString()
                            select dd).Take(count).ToList();

            return ffresult;


        }

        public List<ProductInfo> GetCurrentWeekFocusProductInfo(int count)
        {
            //return (from theProductInfo in m_CosmeticDC.ProductInfos
            //        where theProductInfo.Focus.GetValueOrDefault()
            //        && theProductInfo.RecommendTime.Value >= DateTime.Today.AddDays(-3) && theProductInfo.RecommendTime.Value <= DateTime.Today.AddDays(3)
            //        orderby theProductInfo.RecommendTime descending
            //        select theProductInfo).ToList();

            var result = from theOrderDetail in m_CosmeticDC.OrderDetails
                         join theOrder in m_CosmeticDC.Orders
                             on theOrderDetail.OrderId equals theOrder.OrderId
                         where theOrder.CreateTime >= DateTime.Today.AddDays(-3) && theOrder.CreateTime <= DateTime.Today.AddDays(3)
                         select theOrderDetail;
            var secondresult = (from theItem in result
                                group theItem by theItem.ProductId into g
                                select new
                                {
                                    g.Key,
                                    TotalPrice = g.Count()
                                });
            var finalresult = from item in secondresult
                              orderby item.TotalPrice descending
                              select new
                              {
                                  key = item.Key.ToString()
                              };

            var ffresult = (from it in finalresult
                            join dd in m_CosmeticDC.ProductInfos
                            on it.key equals dd.ProductId.ToString()
                            select dd).Take(count).ToList();

            return ffresult;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetPointProductInfo()
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.Point.GetValueOrDefault()
                    orderby theProductInfo.RecommendTime descending
                    select theProductInfo).ToList();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<Product> GetFavoriteProductInfo(Guid aUserId)
        {
            return (from theProductInfo in m_CosmeticDC.Products
                    join theFavorite in m_CosmeticDC.Favorites on theProductInfo.ProductId equals theFavorite.ProductId
                    where theFavorite.UserId == aUserId
                    orderby theFavorite.CreateTime descending
                    select theProductInfo).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProduct"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByBrand(Product aProduct)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.BrandId == aProduct.BrandId && theProductInfo.ProductId != aProduct.ProductId
                    orderby theProductInfo.CreateTime descending
                    select theProductInfo).ToList();
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }


        /// <summary>
        /// 本类热销for CategoryToProducts.aspx
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByCategoryId(Guid aCategoryId, int count)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductCategoryId == aCategoryId
                          orderby theProduct.Views descending
                          select theProduct).Take(count);
            return result.ToList();
        }

        public List<ProductInfo> GetProductInfoBySupCategoryId(Guid aCategoryId, int count)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategoryId == aCategoryId
                          orderby theProduct.Views descending
                          select theProduct).Take(count);
            return result.ToList();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aBrand"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByBrand(Brand aBrand)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.BrandId == aBrand.BrandId
                    orderby theProductInfo.CreateTime descending
                    select theProductInfo).ToList();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductByCategory_L(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductCategoryId == aCategoryId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCategory"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByFirstCategory(Category aCategory)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategory == aCategory
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        ///
        public List<ProductInfo> GetProductInfoByFirstCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategoryId == aCategoryId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        public List<ProductInfo> GetProductInfoByFirstCategorys(int count)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.counts equals theCategory.Index
                          where theProduct.counts == count
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }


        //public List<ProductInfo> GetProductInfoByFirstCategorys(int  aCategoryId)
        //{
        //    var result = (from theProduct in m_CosmeticDC.ProductInfos
        //                  where theProduct.counts   == aCategoryId
        //                  orderby theProduct.CreateTime descending
        //                  select theProduct).ToList();
        //    return result;
        //}


        /// <summary>
        /// 获得最高级的某个分类的商品
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByTopCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategory.SupCategoryId == aCategoryId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<Product> GetProductByFirstCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.Products
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategory.CategoryId == aCategoryId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="brand"></param>
        /// <param name="price"></param>
        /// <param name="type"></param>
        /// <param name="style"></param>
        /// <returns></returns>
        public DataSet SearchProductInfo(string brand, string price, string type, string style, string order)
        {
            if (brand == "" || price == "" || type == "" || order == "")
            {
                return null;
            }
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            try
            {
                string sql = "select * from ProductInfo where 1=1 ";
                if (brand != "-1")
                {
                    sql += " and 品牌 like '%" + brand + "%' ";
                }
                if (price != "-1")
                {
                    if (price == "9")
                    {
                        sql += " and 标准价格> 400 ";
                    }
                    else if (price == "1")
                    {
                        sql += " and 标准价格>=1 and 标准价格<= 49 ";
                    }
                    else
                    {
                        int intprice = int.Parse(price);
                        sql += " and 标准价格 >= " + ((intprice - 1) * 50).ToString() + "and 标准价格 <= " + (intprice * 50 - 1).ToString();
                    }
                }
                if (type != "-1")
                {
                    sql += " and 类型 like '%" + type + "%'";
                }
                if (style != "-1")
                {
                    sql += " and 功能 like '%" + style + "%'";
                }
                if (order != "")
                {
                    if (order == "PA")
                    {
                        sql += " order by 网络零售价 asc";
                    }
                    else if (order == "PD")
                    {
                        sql += " order by 网络零售价 desc";
                    }
                    else if (order == "CA")
                    {
                        sql += " order by PublishDate asc";
                    }
                    else if (order == "CD")
                    {
                        sql += " order by PublishDate desc";
                    }
                    else
                    {
                        sql += " order by Views desc";
                    }

                }
                SqlCommand com = new SqlCommand(sql, con);

                DataSet ds = new DataSet();

                SqlDataAdapter da = new SqlDataAdapter(com);
                da.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                //con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aName"></param>
        /// <returns></returns>
        public List<Product> SearchProductByName(string aName)
        {
            string[] theNames = aName.Split(' ');
            IQueryable<Product> theProductInfos = m_CosmeticDC.Products;
            List<Product> theResult = new List<Product> ();

            foreach (string theName in theNames)
            {
                theResult.AddRange(SearchProductByName(theProductInfos, theName).ToList());
            }

            return theResult;
        }

        public IQueryable<Product> SearchProductByName(IQueryable<Product> aProductInfos, string aName)
        {
            return (from theProductInfo in aProductInfos
                    where theProductInfo.Name.Contains(aName)// && theProductInfo.procontents == "1"

                    select theProductInfo);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aName"></param>
        /// <returns></returns>
        public List<ProductInfo> SearchProductInfoByName(string aName)
        {
            string[] theNames = aName.Split(' ');
            IQueryable<ProductInfo> theProductInfos = m_CosmeticDC.ProductInfos;
            foreach (string theName in theNames)
            {
                theProductInfos = SearchProductInfoByName(theProductInfos, theName);
            }

            return theProductInfos.ToList();
        }

        public List<HotKeyWord> GetHotKeyWord()
        {
            var result = from theHotkey in m_CosmeticDC.HotKeyWord
                         orderby theHotkey.ViewTimes
                         select theHotkey;
            return result.ToList();
        }

        public IEnumerable GetHotKeyWord(int count)
        {
            var result = from theHotkey in m_CosmeticDC.HotKeyWord
                         orderby theHotkey.ViewTimes
                         select theHotkey;
            return result.Take(count);
        }

        public HotKeyWord GetHotKeyWordById(int id)
        {
            HotKeyWord result = (from theHotkey in m_CosmeticDC.HotKeyWord
                                 where theHotkey.Id == id
                                 select theHotkey).Single();
            return result;
        }

        public void InsertKeyWord(string KeyWord, int sequence)
        {
            int maxid = m_CosmeticDC.HotKeyWord.Max(c=>c.Id);

            HotKeyWord newHotKey = new HotKeyWord();

            newHotKey.CreateTime = DateTime.Now;
            newHotKey.Id = maxid + 1;
            newHotKey.ViewTimes = sequence;
            newHotKey.HotKeyWord1 = KeyWord;
            newHotKey.addressline = "";

            m_CosmeticDC.HotKeyWord.InsertOnSubmit(newHotKey);
            m_CosmeticDC.SubmitChanges();
        }


        public void DeleteKeyWord(int Id)
        {
            HotKeyWord result = (from theHotkey in m_CosmeticDC.HotKeyWord
                                where theHotkey.Id == Id
                                select theHotkey).Single();

            m_CosmeticDC.HotKeyWord.DeleteOnSubmit(result);
            m_CosmeticDC.SubmitChanges();
        }

        public void IncreaseKeyWord(string content)
        {
            var result = from theHotkey in m_CosmeticDC.HotKeyWord
                         where theHotkey.HotKeyWord1 == content
                         select theHotkey;
            if (result.Count() == 0)
            {
                HotKeyWord newkeyword = new HotKeyWord();
                newkeyword.HotKeyWord1 = content;
                newkeyword.CreateTime = DateTime.Now;
                newkeyword.ViewTimes = 1;
                newkeyword.addressline = "http://www.jj-9.com/";

                m_CosmeticDC.HotKeyWord.InsertOnSubmit(newkeyword);
            }
            else
            {
                result.Single().ViewTimes++;
            }

            m_CosmeticDC.SubmitChanges();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductInfos"></param>
        /// <param name="aName"></param>
        /// <returns></returns>
        public IQueryable<ProductInfo> SearchProductInfoByName(IQueryable<ProductInfo> aProductInfos, string aName)
        {
            return (from theProductInfo in aProductInfos
                    where theProductInfo.Name.Contains(aName) && theProductInfo.procontents == "1"

                    select theProductInfo);
        }


        /// <summary>
        /// get specify product skin fit
        /// </summary>
        /// <param name="aProduct">the specify product</param>
        /// <returns>product fit list</returns>
        public List<ProductFit> GetProductSkinFitByProduct(Product aProduct)
        {
            Category theSkinTopCategory = this.m_CategoryManager.GetTopCategryByName(CategoryManager.ETopCategory.UserSkin);
            return (from theProductFit in m_CosmeticDC.ProductFits
                    where theProductFit.ProductId == aProduct.ProductId && theProductFit.FitCategory.SupCategoryId == theSkinTopCategory.CategoryId
                    select theProductFit).ToList();
        }
        /// <summary>
        /// get specify product hair fit
        /// </summary>
        /// <param name="aProduct">the specify product</param>
        /// <returns>product fit list</returns>
        public List<ProductFit> GetProductHairFitByProduct(Product aProduct)
        {
            Category theHairTopCategory = this.m_CategoryManager.GetTopCategryByName(CategoryManager.ETopCategory.UserHair);
            return (from theProductFit in m_CosmeticDC.ProductFits
                    where theProductFit.ProductId == aProduct.ProductId && theProductFit.FitCategory.SupCategoryId == theHairTopCategory.CategoryId
                    select theProductFit).ToList();
        }


        /// <summary>
        /// get the products' instance 
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public IList GetProducts(Hashtable ht)
        {

            Guid[] pp = new Guid[ht.Count];
            int i = 0;
            foreach (DictionaryEntry entry in ht)
            {
                pp[i] = new Guid(entry.Key.ToString());
                i++;
            }

            var result = (from gpt in m_CosmeticDC.ProductInfos
                          where pp.Contains(gpt.ProductId)
                          select new
                          {
                              ProductId = gpt.ProductId,
                              ProductName = gpt.Name,
                              ProductUnitPrice = gpt.网络零售价.ToString(),
                              PictureUrl = gpt.PictureUrl,
                              ProductWeight = gpt.weight
                          }).ToList();

            return result;
        }

        public IList GetProducts_Cos(Hashtable ht)
        {

            Guid[] pp = new Guid[ht.Count];
            int i = 0;
            foreach (DictionaryEntry entry in ht)
            {
                pp[i] = new Guid(entry.Key.ToString());
                i++;
            }

            var result = (from gpt in m_CosmeticDC.Products
                          where pp.Contains(gpt.ProductId)
                          select new
                          {
                              ProductId = gpt.ProductId,
                              ProductName = gpt.Name,
                              ProductUnitPrice = gpt.eprice.ToString(),
                              ProductMarketPrice = gpt.Price.ToString(),
                              ProductRate = gpt.coefficient.ToString(),
                              PictureUrl = gpt.PictureUrl,
                              ProductWeight = gpt.weight,
                              UID = gpt.UID
                          }).ToList();

            return result;
        }

        /// <summary>
        /// 显示捆绑商品。
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public IList GetProducts_RH(Hashtable ht)
        {
            Guid[] pp = new Guid[ht.Count];
            int i = 0;
            foreach (DictionaryEntry entry in ht)
            {
                pp[i] = new Guid(entry.Key.ToString());
                i++;
            }

            var result = (from gpt in m_CosmeticDC.Products
                          join dis in m_CosmeticDC.ProductDiscounts
                          on gpt.ProductId equals dis.ProductId
                          where pp.Contains(gpt.ProductId)
                          select new
                          {
                              ProductId = gpt.ProductId,
                              ProductName = gpt.Name,
                              ProductUnitPrice = dis.discountPrice.ToString(),
                              ProductMarketPrice = gpt.Price.ToString(),
                              ProductRate = gpt.coefficient.ToString(),
                              PictureUrl = gpt.PictureUrl,
                              ProductWeight = gpt.weight,
                              UID = gpt.UID
                          }).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public List<ProductInfo> GetPackageProducts(Hashtable ht)
        {
            Guid[] pp = new Guid[ht.Count];
            int i = 0;

            foreach (DictionaryEntry entry in ht)
            {
                pp[i] = new Guid(entry.Key.ToString());
                i++;
            }
            List<ProductInfo> pis = new List<ProductInfo>();

            List<PackageSale> result = (from gpt in m_CosmeticDC.PackageSales
                                        where pp.Contains(gpt.Id)
                                        select gpt).ToList();
            foreach (PackageSale item in result)
            {
                ProductInfo pi1 = (from pinfo in m_CosmeticDC.ProductInfos
                                   where pinfo.ProductId == item.ProductId1
                                   select pinfo).Single();
                ProductInfo pi2 = (from pinfo in m_CosmeticDC.ProductInfos
                                   where pinfo.ProductId == item.ProductId2
                                   select pinfo).Single();
                ProductInfo pi3 = (from pinfo in m_CosmeticDC.ProductInfos
                                   where pinfo.ProductId == item.ProductId3
                                   select pinfo).Single();
                pis.Add(pi1);
                pis.Add(pi2);
                pis.Add(pi3);
            }

            return pis;


        }

        public decimal GetDeliveryPriceByWeightAndArea(int weight, string area)
        {
            var result = from post in m_CosmeticDC.Postages
                         where post.City == area
                         select post;
            if (result == null)
            {
                return 0;
            }

            Postage aPostage = result.Single();

            return aPostage.Increament.Value * (weight - 1) + 20;

        }

        public List<Postage> GetPostageAll()
        {
            var result = from post in m_CosmeticDC.Postages
                         select post;

            return result.ToList();
        }

        public void UpdatePostage(string id, string under500g, string Increament)
        {
            Postage po = (from post in m_CosmeticDC.Postages
                          where post.Id == int.Parse(id)
                          select post).Single();
            po.Under500g = decimal.Parse(under500g);
            po.Increament = decimal.Parse(Increament);

            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// get tag by product from all the product comments
        /// </summary>
        /// <param name="aProduct">the product</param>
        /// <returns>tag list</returns>
        [Obsolete("", true)]
        public List<Tag> GetTagByProduct(Product aProduct)
        {
            var theTagGroup = from theTag in m_CosmeticDC.Tags
                              join theCommentTag in m_CosmeticDC.CommentTags on theTag.TagId equals theCommentTag.TagId
                              where theCommentTag.Comment.ProductId == aProduct.ProductId
                              group theCommentTag by theCommentTag.TagId;

            List<Tag> theTagList = new List<Tag>();
            foreach (var theTag in theTagGroup)
            {
                Tag theNewTag = new Tag();
                theNewTag.Counts = theTag.Count();
                theNewTag.Name = theTag.ToList()[0].Tag.Name;
                theNewTag.TagId = theTag.ToList()[0].Tag.TagId;
                theTagList.Add(theNewTag);
            }
            return theTagList.OrderByDescending(theTag => theTag.Counts).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ProductId"></param>
        /// <returns></returns>
        public bool CheckProductStock(Guid ProductId)
        {
            var result = from ginfo in m_CosmeticDC.ProductInfos
                         where ginfo.ProductId == ProductId
                         select ginfo;
            //如果视图 ProductInfo 里没有该商品
            if (result.Count() == 0)
            {
                return false;
            }

            if (result.Single().在库数量 != 0)
            {//如果该产品在库数量不为0，返回true
                return true;
            }
            else
            {//否则返回false
                return false;
            }
        }

        public bool CheckProductCategory(string Pid, string CategoryName)
        {
            string CategoryId = (from category in m_CosmeticDC.Categories
                                 where category.Name == CategoryName
                                 select category.CategoryId.ToString()).Single();

            string ProductCategoryId = (from product in m_CosmeticDC.Products
                                        where product.ProductId == new Guid(Pid)
                                        select product.ProductCategoryId.ToString()).Single();

            List<string> middleCategoryIds = (from cate in m_CosmeticDC.Categories
                                              where cate.SupCategoryId == new Guid(CategoryId)
                                              select cate.CategoryId.ToString()).ToList();

            if (middleCategoryIds.Contains(ProductCategoryId))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #region Search

        /// <summary>
        /// search product
        /// </summary>
        /// <param name="aParameters">search keyward dictionary. ie: Name:美白, Size:100ml</param>
        /// <returns>product list</returns>
        //public List<Product> ProductSearch(Dictionary<string,string> aParameters)
        //{
        //    IQueryable<Product> theProductList	= m_CosmeticDC.Products;

        //    foreach (string theKey in aParameters.Keys)
        //    {
        //        string theValue		= aParameters[theKey];
        //        if (theKey.Equals("Name"))
        //        {
        //            foreach (string theKeyWord in theValue.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
        //            {
        //                theProductList	= (from theProduct in theProductList
        //                                   where theProduct.Name.Contains(theKeyWord)
        //                                   select theProduct);
        //            }
        //        }
        //        else if (theKey.Equals("Tag"))
        //        {
        //            foreach (string theKeyWord in theValue.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
        //            {
        //                theProductList	= (from theProduct in theProductList
        //                                   join theProductTag in m_CosmeticDC.ProductTags on theProduct.ProductId equals theProductTag.ProductId
        //                                   where theProductTag.Tag.Name == theKeyWord
        //                                   select theProduct);
        //            }
        //        }
        //        else if (theKey.Equals("Comment"))
        //        {
        //            foreach (string theKeyWord in theValue.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
        //            {
        //                theProductList	= (from theProduct in theProductList
        //                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        //                                   where theCommand.Content.Contains(theKeyWord)
        //                                   select theProduct);
        //            }
        //        }
        //        else if (theKey.Equals("Brand"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   where  theProduct.BrandId == new Guid(theValue)
        //                                   select theProduct);
        //        }
        //        else if (theKey.Equals("Series"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   where  theProduct.ProductSeriesId == new Guid(theValue)
        //                                   select theProduct);
        //        }
        //        else if (theKey.Equals("ProductCategory"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   where  theProduct.ProductCategoryId == new Guid(theValue)
        //                                   select theProduct);
        //        }
        //        else if (theKey.Equals("Published"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   where  theProduct.PublishDate.AddMonths(Convert.ToInt32(theValue)) > DateTime.Today
        //                                   select theProduct);
        //        }
        //        else if (theKey.Equals("Age"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        //                                   where Convert.ToInt32(theValue)-10 < (DateTime.Today-theCommand.User.Birthday).Days/365
        //                                   && (DateTime.Today-theCommand.User.Birthday).Days/365 < Convert.ToInt32(theValue)
        //                                   select theProduct);
        //        }
        //        else if (theKey.Equals("Hair"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        //                                   where theCommand.User.UserHairCategoryId == new Guid(theValue)
        //                                   select theProduct);
        //        }
        //        else if (theKey.Equals("Skin"))
        //        {
        //            theProductList	= (from theProduct in theProductList
        //                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        //                                   where theCommand.User.UserSkinCategoryId == new Guid(theValue)
        //                                   select theProduct);
        //        }
        //    }

        //    return theProductList.Distinct().ToList();
        //}

        #endregion





        #region Product Tag

        /// <summary>
        /// get product tag by product tag id
        /// </summary>
        /// <param name="aProductTagId">the special product tag id</param>
        /// <returns>product tag</returns>
        public ProductTag GetProductTagById(Guid aProductTagId)
        {
            List<ProductTag> theTagList = (from theProductTag in m_CosmeticDC.ProductTags
                                           where theProductTag.ProductTagId == aProductTagId
                                           select theProductTag).ToList();

            if (theTagList.Count == 1)
            {
                return theTagList[0];
            }
            return null;
        }
        /// <summary>
        /// get product tag by tag id and product id
        /// </summary>
        /// <param name="aTagId">the special tag id</param>
        /// <param name="aProductId">the special product id</param>
        /// <returns>product tag</returns>
        public static ProductTag GetProductTagById(CosmeticORMDataContext m_CosmeticDC, Guid aTagId, Guid aProductId)
        {
            List<ProductTag> theTagList = (from theProductTag in m_CosmeticDC.ProductTags
                                           where theProductTag.TagId == aTagId && theProductTag.ProductId == aProductId
                                           select theProductTag).ToList();
            if (theTagList.Count == 1)
            {
                return theTagList[0];
            }
            return null;
        }

        /// <summary>
        /// the special product tag counts+1
        /// </summary>
        /// <param name="aProductTag">the special product tag</param>
        public static void ProductTagAddCount(CosmeticORMDataContext m_CosmeticDC, ProductTag aProductTag)
        {
            aProductTag.Counts++;
            aProductTag.LastUseTime = DateTime.Now;
            m_CosmeticDC.SubmitChanges();
        }
        /// <summary>
        /// the special product tag counts+1
        /// </summary>
        /// <param name="aTagId">the tag id</param>
        /// <param name="aProductId">product id</param>
        public static void ProductTagAddCount(CosmeticORMDataContext m_CosmeticDC, Guid aTagId, Guid aProductId)
        {
            ProductTag theProductTag = GetProductTagById(m_CosmeticDC, aTagId, aProductId);
            if (null != theProductTag)
            {
                theProductTag.Counts++;
                theProductTag.LastUseTime = DateTime.Now;
                m_CosmeticDC.SubmitChanges();
            }
            else
            {
                ProductTag theNewProductTag = new ProductTag();
                theNewProductTag.Counts = 1;
                theNewProductTag.CreateTime = DateTime.Now;
                theNewProductTag.LastUseTime = DateTime.Now;
                theNewProductTag.ProductId = aProductId;
                theNewProductTag.ProductTagId = Guid.NewGuid();
                theNewProductTag.TagId = aTagId;
                m_CosmeticDC.ProductTags.InsertOnSubmit(theNewProductTag);
                m_CosmeticDC.SubmitChanges();
            }
        }


        /// <summary>
        /// the special product tag counts-1
        /// </summary>
        /// <param name="aProductTag">the special product tag</param>
        public static void ProductTagMinusCount(CosmeticORMDataContext m_CosmeticDC, ProductTag aProductTag)
        {
            if (null == aProductTag)
            {
                return;
            }

            if (aProductTag.Counts == 1)
            {
                m_CosmeticDC.ProductTags.DeleteOnSubmit(aProductTag);
            }
            else
            {
                aProductTag.Counts--;
            }
            m_CosmeticDC.SubmitChanges();
        }
        /// <summary>
        /// the special product tag counts-1
        /// </summary>
        /// <param name="aTagId">the tag id</param>
        /// <param name="aProductId">product id</param>
        public static void ProductTagMinusCount(CosmeticORMDataContext m_CosmeticDC, Guid aTagId, Guid aProductId)
        {
            ProductTagMinusCount(m_CosmeticDC, GetProductTagById(m_CosmeticDC, aTagId, aProductId));
        }
        /*
                public static void ProductTagMinusCount(string aTagName, Guid aProductId)
                {
                    Tag theTag	= this.m_TagManager.GetTagByName(aTagName, TagManager.ETagCategory.Product);
                    this.ProductTagMinusCount(theTag.TagId, aProductId);
                }
        */


        /// <summary>
        /// get product tag by product
        /// </summary>
        /// <param name="aProductId">product id</param>
        /// <returns>product tag list</returns>
        public List<ProductTag> GetTagByProduct(Guid aProductId)
        {
            return (from theProductTag in m_CosmeticDC.ProductTags
                    where theProductTag.ProductId == aProductId
                    && theProductTag.Counts > 0
                    orderby theProductTag.Counts descending
                    select theProductTag).ToList();
        }

        #endregion




        #region Recommend Produt

        /// <summary>
        /// search recommand product
        /// </summary>
        /// <returns>product list</returns>
        public List<RecommendProduct> GetAllRecommendProducts()
        {
            return (from theRecommend in m_CosmeticDC.RecommendProducts
                    select theRecommend).ToList();
        }

        /// <summary>
        /// search recommand product
        /// </summary>
        /// <returns>product list</returns>
        public List<RecommendProduct> GetRecommendProducts()
        {
            return (from theRecommend in m_CosmeticDC.RecommendProducts
                    where theRecommend.Recommend
                    select theRecommend).ToList();
        }




        /// <summary>
        /// search recommand product
        /// </summary>
        /// <returns>product list</returns>
        public List<RecommendProduct> GetFocusProducts()
        {
            return (from theRecommend in m_CosmeticDC.RecommendProducts
                    where theRecommend.Focus
                    select theRecommend).ToList();
        }

        /// <summary>
        /// search point product
        /// </summary>
        /// <returns>product list</returns>
        public List<RecommendProduct> GetPointProducts()
        {
            return (from theRecommend in m_CosmeticDC.RecommendProducts
                    where theRecommend.Point
                    select theRecommend).ToList();
        }


        /// <summary>
        /// insert new recommend product into db
        /// </summary>
        /// <param name="aRecommendProduct">new recommend product</param>
        public void AddRecommendProduct(RecommendProduct aRecommendProduct)
        {
            if (m_CosmeticDC.RecommendProducts.Where(theRecommendProduct => theRecommendProduct.ProductId == aRecommendProduct.ProductId).Count() == 0)
            {
                m_CosmeticDC.RecommendProducts.InsertOnSubmit(aRecommendProduct);
                m_CosmeticDC.SubmitChanges();
            }
        }

        /// <summary>
        /// delete a recommend product
        /// </summary>
        /// <param name="aRecommendProductId">Recommend Product Id</param>
        public void DeleteRecommendProduct(Guid aRecommendProductId)
        {
            m_CosmeticDC.RecommendProducts.DeleteOnSubmit(GetRecommendProductById(aRecommendProductId));
            m_CosmeticDC.SubmitChanges();

        }

        /// <summary>
        /// get recommend product by id
        /// </summary>
        /// <param name="aRecommendProductId"></recommend product id>
        /// <returns>recommend product</returns>
        public RecommendProduct GetRecommendProductById(Guid aRecommendProductId)
        {
            return m_CosmeticDC.RecommendProducts.FirstOrDefault(theRecommend => theRecommend.RecommendProductId == aRecommendProductId);
        }


        /// <summary>
        /// get recommend product by id
        /// </summary>
        /// <param name="aRecommendProductId"></recommend product id>
        /// <returns>recommend product</returns>
        public RecommendProduct GetRecommendProductByProductId(Guid aProductId)
        {
            return m_CosmeticDC.RecommendProducts.FirstOrDefault(theRecommend => theRecommend.ProductId == aProductId);
        }
        #endregion



        public void DeletePlateJapaneseCosmetic(int Id)
        {
            PlateJapaneseCosmetic pjc = (from theJC in m_CosmeticDC.PlateJapaneseCosmetics
                                         where theJC.Id == Id
                                         select theJC).Single();
            m_CosmeticDC.PlateJapaneseCosmetics.DeleteOnSubmit(pjc);
            m_CosmeticDC.SubmitChanges();
        }

        public void DeletePlateHouseHold(int Id)
        {
            PlateHouseHold phh = (from theJC in m_CosmeticDC.PlateHouseHolds
                                  where theJC.Id == Id
                                  select theJC).Single();
            m_CosmeticDC.PlateHouseHolds.DeleteOnSubmit(phh);
            m_CosmeticDC.SubmitChanges();
        }

        public void DeletePlateExcellentCloth(int Id)
        {
            PlateExcellentCloth pec = (from theJC in m_CosmeticDC.PlateExcellentCloths
                                       where theJC.Id == Id
                                       select theJC).Single();
            m_CosmeticDC.PlateExcellentCloths.DeleteOnSubmit(pec);
            m_CosmeticDC.SubmitChanges();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetPlateJapaneseCosmetic()
        {
            var result = (from theJC in m_CosmeticDC.PlateJapaneseCosmetics
                          join theProductInfo in m_CosmeticDC.ProductInfos
                          on theJC.ProductId equals theProductInfo.ProductId
                          orderby theJC.Sequence
                          select new
                          {
                              Id = theJC.Id,
                              ProductId = theProductInfo.ProductId,
                              ProductName = theProductInfo.Name,
                              PictureUrl = theProductInfo.PictureUrl,
                              ProductView = theProductInfo.Views,
                              ProductComment = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == theProductInfo.ProductId select theProduct.Comments.Count).Single()
                          }
                         ).Distinct();
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetPlateHouseHold()
        {
            var result = (from theJC in m_CosmeticDC.PlateHouseHolds
                          join theProductInfo in m_CosmeticDC.ProductInfos
                          on theJC.ProductId equals theProductInfo.ProductId
                          orderby theJC.Sequence
                          select new
                          {
                              Id = theJC.Id,
                              ProductId = theProductInfo.ProductId,
                              ProductName = theProductInfo.Name,
                              PictureUrl = theProductInfo.PictureUrl,
                              ProductView = theProductInfo.Views,
                              ProductComment = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == theProductInfo.ProductId select theProduct.Comments.Count).Single()
                          }
                         ).Distinct();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetPlateExcellentCloth()
        {
            var result = (from theJC in m_CosmeticDC.PlateExcellentCloths
                          join theProductInfo in m_CosmeticDC.ProductInfos
                          on theJC.ProductId equals theProductInfo.ProductId
                          orderby theJC.Sequence
                          select new
                          {
                              Id = theJC.Id,
                              ProductId = theProductInfo.ProductId,
                              ProductName = theProductInfo.Name,
                              PictureUrl = theProductInfo.PictureUrl,
                              ProductView = theProductInfo.Views,
                              ProductComment = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == theProductInfo.ProductId select theProduct.Comments.Count).Single()
                          }
                         ).Distinct();
            return result;
        }


        public void AddProductToJapaneseProduct(string productid)
        {
            var result = from theCosmetic in m_CosmeticDC.PlateJapaneseCosmetics
                         where theCosmetic.ProductId == new Guid(productid)
                         select theCosmetic;
            if (result.Count() == 0)
            {
                PlateJapaneseCosmetic pjc = new PlateJapaneseCosmetic();
                pjc.CreateTime = DateTime.Now;
                pjc.ProductId = new Guid(productid);
                pjc.Sequence = (from thepjc in m_CosmeticDC.PlateJapaneseCosmetics orderby thepjc.Sequence descending select thepjc).Take(1).Single().Sequence + 1;

                m_CosmeticDC.PlateJapaneseCosmetics.InsertOnSubmit(pjc);
                m_CosmeticDC.SubmitChanges();
            }
        }

        public void AddProductToHouseHold(string productid)
        {
            var result = from theHouseHold in m_CosmeticDC.PlateHouseHolds
                         where theHouseHold.ProductId == new Guid(productid)
                         select theHouseHold;
            if (result.Count() == 0)
            {
                PlateHouseHold phh = new PlateHouseHold();

                phh.CreateTime = DateTime.Now;
                phh.ProductId = new Guid(productid);
                phh.Sequence = (from thephh in m_CosmeticDC.PlateHouseHolds orderby thephh.Sequence descending select thephh).Take(1).Single().Sequence + 1;

                m_CosmeticDC.PlateHouseHolds.InsertOnSubmit(phh);
                m_CosmeticDC.SubmitChanges();
            }
        }

        public void AddProductToCloth(string productid)
        {
            var result = from theCloth in m_CosmeticDC.PlateExcellentCloths
                         where theCloth.ProductId == new Guid(productid)
                         select theCloth;
            if (result.Count() == 0)
            {
                //PlateHouseHold phh = new PlateHouseHold();
                PlateExcellentCloth pec = new PlateExcellentCloth();

                pec.CreateTime = DateTime.Now;
                pec.ProductId = new Guid(productid);
                pec.Sequence = (from thepec in m_CosmeticDC.PlateExcellentCloths orderby thepec.Sequence descending select thepec).Take(1).Single().Sequence + 1;

                m_CosmeticDC.PlateExcellentCloths.InsertOnSubmit(pec);
                m_CosmeticDC.SubmitChanges();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aName"></param>
        /// <returns></returns>



        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductInfos"></param>
        /// <param name="aName"></param>
        /// <returns></returns>
        public List<ProductInfo> SearchProductInfoByPrice(decimal priceBlow, decimal priceTop)
        {
            IQueryable<ProductInfo> theProductInfos = m_CosmeticDC.ProductInfos;
            return (from theProductInfo in theProductInfos
                    where theProductInfo.网络零售价 >= priceBlow & theProductInfo.网络零售价 <= priceTop & theProductInfo.procontents == "1"
                    select theProductInfo).ToList();
        }

        /// <summary>
        /// 得到到该用户所有的商品评论。
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static List<EntityComment> GetAllCommentByUserId(string userId)
        {
            List<EntityComment> listComment = new List<EntityComment>();
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdstr = "select com.[ProductId],com.[CreateTime], proIn.[Name],proIn.[PictureUrl],com.[Content] from dbo.ProductInfo as proIn inner join dbo.Comment as com on proIn.[ProductId] = com.[ProductId] where com.[UserId]='" + userId + "'";
                SqlCommand cmd = new SqlCommand(cmdstr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    EntityComment thecom = new EntityComment();
                    thecom.Content = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Content"].ToString()) ? " " : ds.Tables[0].Rows[i]["Content"].ToString();
                    thecom.CreateTime = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["CreateTime"].ToString()) ? "2009-1-1" : ds.Tables[0].Rows[i]["CreateTime"].ToString();
                    thecom.Name = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Name"].ToString()) ? "  " : ds.Tables[0].Rows[i]["Name"].ToString();
                    thecom.PictureUrl = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["PictureUrl"].ToString()) ? "  " : ds.Tables[0].Rows[i]["PictureUrl"].ToString();
                    thecom.ProductId = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ProductId"].ToString()) ? "  " : ds.Tables[0].Rows[i]["ProductId"].ToString();
                    listComment.Add(thecom);
                }
                return listComment;
            }
            finally
            {
                con.Close();
            }

        }

        /// <summary>
        /// Search products by price. 10.19
        /// </summary>
        /// <param name="priceBlow"></param>
        /// <param name="priceTop"></param>
        /// <returns></returns>
        public List<ProductInfo> SearchProductInfoByPrice(decimal priceBlow, decimal priceTop, string contents)
        {
            IQueryable<ProductInfo> theProductInfos = m_CosmeticDC.ProductInfos;
            return (from theProductInfo in theProductInfos
                    where theProductInfo.网络零售价 >= priceBlow & theProductInfo.网络零售价 <= priceTop & theProductInfo.procontents == "1"
                    select theProductInfo).ToList();
        }

        /// <summary>
        /// 将商品插入本月热销表。
        /// </summary>
        /// <param name="productId"></param>
        public void InsertMonthSale(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();

                string cmdStrGetSe = "select top 1(Sequence) from dbo.MonthlySales order by Sequence desc";
                SqlCommand cmdSe = new SqlCommand(cmdStrGetSe, con);
                object sequence = cmdSe.ExecuteScalar();
                int m_sequence = 0;
                if (!string.IsNullOrEmpty(sequence.ToString()))
                {
                    m_sequence = int.Parse(sequence.ToString());
                }
                m_sequence++;
                string cmdStr = "INSERT INTO [dbo].[MonthlySales]([ProductId] ,[Sequence])  VALUES ('" + productId + "' ," + m_sequence + ")";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 得到所有的本月执销。
        /// </summary>
        public DataSet SelectAllMonthSale()
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ms.Id, pro.[PictureUrl],pro.[Name],ms.[Sequence],ms.[ProductId] from dbo.[MonthlySales] as ms inner join dbo.[ProductInfo] as pro on ms.[ProductId] = pro.[ProductId] order by ms.Sequence";
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 取消该商品的本月热销。
        /// </summary>
        /// <param name="productId"></param>
        public void DeleteMonthSale(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "delete from [dbo].[MonthlySales] where ProductId ='" + productId + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        public void UpdateMonthSale(string Id, int Sequence)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "update [dbo].[MonthlySales] set Sequence = '" + Sequence + "' where Id ='" + Id + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 取消该商品的本周热销。
        /// </summary>
        /// <param name="productId"></param>
        public void DeleteWeeklySale(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "delete from [dbo].[WeeklySales] where ProductId ='" + productId + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        public void UpdateWeeklySale(string Id, int sequence)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "update [dbo].[WeeklySales] set Sequence = '" + sequence + "'where Id ='" + Id + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 得到所有的本周执销。
        /// </summary>
        public DataSet SelectAllWeeklySale()
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ms.Id, pro.[PictureUrl],pro.[Name],ms.[Sequence],ms.[ProductId] from dbo.[WeeklySales] as ms inner join dbo.[ProductInfo] as pro on ms.[ProductId] = pro.[ProductId] order by ms.Sequence";
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 将商品插入本周热销表。
        /// </summary>
        /// <param name="productId"></param>
        public void InsertWeeklySale(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();

                string cmdStrGetSe = "select top 1(Sequence) from dbo.WeeklySales order by Sequence desc";
                SqlCommand cmdSe = new SqlCommand(cmdStrGetSe, con);
                object sequence = cmdSe.ExecuteScalar();
                int m_sequence = 0;
                if (!string.IsNullOrEmpty(sequence.ToString()))
                {
                    m_sequence = int.Parse(sequence.ToString());
                }
                m_sequence++;
                string cmdStr = "INSERT INTO [dbo].[WeeklySales]([ProductId] ,[Sequence])  VALUES ('" + productId + "' ," + m_sequence + ")";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 将商品插入组合搭配表。
        /// </summary>
        /// <param name="productId"></param>
        public void InsertPackageSales(string productId1, string productId2, string productId3, decimal allprice)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();

                string cmdStr = "INSERT INTO [dbo].[PackageSales]([Id] ,[ProductId1] ,[ProductId2],[ProductId3] ,[CreateTime] ,[CurrentPrice],[ViewTimes]) VALUES(newid() ,'" + productId1 + "','" + productId2 + "','" + productId3 + "',getdate() ," + allprice + " ,0)";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// select productInfo by product id。
        /// </summary>
        /// <param name="productId"></param>
        public DataSet SelectProductInforByProductId(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();

                string cmdStr = "select ProductId,[Name],PictureUrl,[网络零售价] as price from ProductInfo where ProductId='" + productId + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 删除指定的搭配的商品。
        /// </summary>
        /// <param name="productId"></param>
        public void DeleteThePackageSales(string id)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "delete PackageSales where Id='" + id + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 获得所有搭配的商品。
        /// </summary>
        /// <param name="productId"></param>
        public DataTable SelectAllPackageSales()
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select Id, (select [Name] from ProductInfo where ProductId= ProductId1) as Name1,(select [Name] from ProductInfo where ProductId= ProductId2) as Name2,(select [Name] from ProductInfo where ProductId= ProductId3) as Name3,CurrentPrice,ViewTimes from PackageSales ";
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }

        public static DataTable SearchProductWithMultileContidion(string Keyword, string CateCon, string MinPrice, string MaxPrice, string pro)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();


                string cmdStr = "select * from ProductInfo where procontents='" + pro + "' ";

                if (null != Keyword && Keyword != "")
                {
                    cmdStr += " and [Name] like '%" + Keyword + "%'  ";
                }

                if (null != CateCon && CateCon != "")
                {
                    cmdStr += " and ProductCategoryId in (" + CateCon + ") ";
                }

                if (null != MinPrice && MinPrice != "")
                {
                    cmdStr += " and 网络零售价 >= " + MinPrice + " ";
                }
                if (null != MaxPrice && MaxPrice != "")
                {
                    cmdStr += " and 网络零售价 <= " + MaxPrice + " ";
                }
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 获得最高级的某个分类的商品，是本类热销的商品的ProductId
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByProductId(Guid aProductId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos

                          where theProduct.ProductId == aProductId

                          select theProduct).ToList();
            return result;
        }

        public IList GetGoodChoiceProducts()
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theGoodChoece in m_CosmeticDC.GoodChoices
                          on theProduct.ProductId equals theGoodChoece.ProductId
                          orderby theGoodChoece.Sequcnce ascending
                          select new
                          {
                              ProductId = theProduct.ProductId,
                              Name = theProduct.Name,
                              PictureUrl = theProduct.PictureUrl,
                              Price = theProduct.网络零售价,
                              Views = theProduct.Views,
                              Sequence = theGoodChoece.Sequcnce,
                              Id = theGoodChoece.Id
                          }
                          ).ToList();
            return result;
        }

        public void UpdateGoodChoiceProducts(string Id, int Sequence)
        {
            var result = (from theGoodChoece in m_CosmeticDC.GoodChoices
                          where theGoodChoece.Id == new Guid(Id)
                          select theGoodChoece).Single();
            result.Sequcnce = Sequence;
            m_CosmeticDC.SubmitChanges();
        }


        public void InsertGoodChoice(Guid ProductId)
        {
            GoodChoice gc = new GoodChoice();
            gc.CreateTime = DateTime.Now;
            gc.Id = Guid.NewGuid();
            gc.ProductId = ProductId;
            gc.Sequcnce = (from theGC in m_CosmeticDC.GoodChoices orderby theGC.Sequcnce descending select theGC.Sequcnce).First().Value + 1;


            m_CosmeticDC.GoodChoices.InsertOnSubmit(gc);
            m_CosmeticDC.SubmitChanges();
        }

        public void DeleteGoodChoiceProduct(Guid ProductId)
        {
            var result = (from thegc in m_CosmeticDC.GoodChoices
                          where thegc.ProductId == ProductId
                          select thegc).Single();

            m_CosmeticDC.GoodChoices.DeleteOnSubmit(result);
            m_CosmeticDC.SubmitChanges();
        }

    }
}
